package org.gensokyo.crawler

import java.io.File
import java.text.{SimpleDateFormat, DecimalFormat}
import java.time.{LocalDateTime, LocalDate}
import java.util.Date

import com.typesafe.scalalogging.slf4j.StrictLogging
import org.apache.commons.io.{FileUtils, IOUtils}
import org.apache.poi.ss.usermodel.{Cell, WorkbookFactory}

import scala.collection.mutable.ListBuffer


/**
  * Created by liusen on 17-3-4.
  */
object Start2 extends StrictLogging {

  def deal1 = {
    val f1 = FileUtils.openInputStream(new File( """/media/liusen/4D14-E845/list-新料.xlsx"""))
    val f2 = FileUtils.openInputStream(new File( """/media/liusen/4D14-E845/异地调离通知单跟踪表 (SEDL)FY1314.xls"""))
    var lll: List[(String, String, String)] = List.empty
    val wb1 = WorkbookFactory.create(f2)
    try {
      val sheetList = List("BJ", "TJ")
      lll = sheetList.flatMap(x => {
        val sheet = wb1.getSheet(x)
        (1 to sheet.getLastRowNum).toList
          .filter(e => sheet.getRow(e) != null
            && sheet.getRow(e).getCell(6) != null
            && sheet.getRow(e).getCell(6).toString.length > 0)
          .map(i => {
            logger.debug(s"sheetName:${x} rowNumber:${i} 物料号:${sheet.getRow(i).getCell(6)}")
            (getStringFromCell(sheet.getRow(i).getCell(6)),
              getDateFromCell(sheet.getRow(i).getCell(16)),
              getStringFromCell(sheet.getRow(i).getCell(4))
              )
          })
      })
      val str = lll.map(x => x._1 + "|" + x._2 + "|" + x._3).mkString("\n")
      FileUtils.writeStringToFile(new File("qingdan.txt"), str)
      logger.debug(s"str:\n${str}")
    } finally {
      wb1.close()
    }

    val wb2 = WorkbookFactory.create(f1)
    try {
      val sheet = wb2.getSheet("Sheet1")
      (1 to sheet.getLastRowNum).toList
        .filter(e => sheet.getRow(e) != null)
        .foreach(i => {
          val num = sheet.getRow(i).getCell(1).toString
          logger.debug(s"rowNumber:${i} 物料号:${num}")
          val stdList = lll.filter(_._1 == num)
          if (stdList.length >= 2) {
            logger.error(s"多个物料号")
            //            throw new Exception("多个物料号")
          } else if (stdList.length == 1) {
            val std = stdList.head
            logger.debug(s"set row ${i} value:${std._2} ${std._3}")
            sheet.getRow(i).getCell(2).setCellValue(std._2)
            sheet.getRow(i).getCell(3).setCellValue(std._3)
          }
        })

      val xlsStream = FileUtils.openOutputStream(new File( """/media/liusen/4D14-E845/1.xlsx"""))
      wb2.write(xlsStream)
    } finally {
      wb2.close()
    }

  }

  def deal2 = {
    val f1 = FileUtils.openInputStream(new File( """/home/liusen/Downloads/1.xlsx"""))
    val f2 = FileUtils.openInputStream(new File( """/home/liusen/Downloads/2.xlsx"""))
    var lll = Map.empty[String, String]
    val wb1 = WorkbookFactory.create(f1)
    try {
      val sheet = wb1.getSheet("Sheet1")
      val r1 = """([0-9]+)""".r
      lll = (1 to sheet.getLastRowNum).toList
        .filter(e => sheet.getRow(e) != null
          && sheet.getRow(e).getCell(0) != null
          && sheet.getRow(e).getCell(0).toString.length > 0)
        .flatMap(e => sheet.getRow(e).getCell(0).toString match {
          case r1(c) => Some(e)
          case _ => None
        }).map(x => {
        (getStringFromCell(sheet.getRow(x).getCell(0)), getStringFromCell(sheet.getRow(x).getCell(1)))
      }).groupBy(_._1).toList.map(x => (x._1, x._2.distinct)).map(x => (x._1, x._2.head._2)).toMap
      logger.debug(s"ll:${lll.mkString("\n")}")
    } finally {
      wb1.close()
    }

    val wb2 = WorkbookFactory.create(f2)
    try {
      val sheet = wb2.getSheet("Sheet1")
      (1 to sheet.getLastRowNum).toList
        .filter(e => sheet.getRow(e) != null
          && sheet.getRow(e).getCell(0) != null
          && getStringFromCell(sheet.getRow(e).getCell(0)) != null
          && sheet.getRow(e).getCell(0).toString.length > 0)
        .foreach(i => {
          val key = getStringFromCell(sheet.getRow(i).getCell(0))
          val value = getStringFromCell(sheet.getRow(i).getCell(1))
          logger.debug(s"rowNumber:${i} key=${key} value:${value}")
          lll.get(key) match {
            case Some(stdValue) => if (stdValue != value) {
              logger.debug(s"write stdValue:${stdValue}")
              sheet.getRow(i).createCell(2).setCellValue(stdValue)
            }
            case _ =>
          }
        })

      val xlsStream = FileUtils.openOutputStream(new File( """/home/liusen/Downloads/3.xlsx"""))
      wb2.write(xlsStream)
    } finally {
      wb2.close()
    }

  }

  val decimalFormat = new DecimalFormat("0")
  val dateFormat = new SimpleDateFormat("dd-MMM-yyy")

  def getStringFromCell(cell: Cell): String = {
    if (cell == null) {
      ""
    } else {
      if (cell.getCellType == 0 || (cell.getCellType == 2 && cell.getCachedFormulaResultType == 0)) {
        decimalFormat.format(cell.getNumericCellValue)
      } else if (cell.getCellType == 1 || (cell.getCellType == 2 && cell.getCachedFormulaResultType == 1)) {
        cell.getStringCellValue
      } else if (cell.getCellType == 4 || (cell.getCellType == 2 && cell.getCachedFormulaResultType == 4)) {
        throw new Exception(s"cell.getCellType:${cell.getCellType} is not date type!")
      } else if (cell.getCellType == 5 || (cell.getCellType == 2 && cell.getCachedFormulaResultType == 5)) {
        throw new Exception("cell has error !")
      } else if (cell.getCellType == 3) {
        ""
      } else {
        throw new Exception(s"cell.getCellType:${cell.getCellType} is not date type!")
      }
    }
  }

  def getDateFromCell(cell: Cell): String = {
    if (cell == null) {
      ""
    } else {
      if (cell.getCellType == 0 || (cell.getCellType == 2 && cell.getCachedFormulaResultType == 0)) {
        cell.toString
      } else if (cell.getCellType == 1 || (cell.getCellType == 2 && cell.getCachedFormulaResultType == 1)) {
        cell.toString
      } else if (cell.getCellType == 4 || (cell.getCellType == 2 && cell.getCachedFormulaResultType == 4)) {
        dateFormat.format(cell.getDateCellValue)
      } else if (cell.getCellType == 5 || (cell.getCellType == 2 && cell.getCachedFormulaResultType == 5)) {
        throw new Exception(s"cell.getCellType:${cell.getCellType} is not date type!")
      } else if (cell.getCellType == 3) {
        throw new Exception(s"cell.getCellType:${cell.getCellType} is not date type!")
      } else {
        throw new Exception(s"cell.getCellType:${cell.getCellType} is not date type!")
      }
    }
  }

  def main(args: Array[String]) {
    deal2
  }
}
